#!/usr/bin/env python
# coding:utf-8
"""
Created on 2017/7/5 16:50

base Info
"""
__author__ = 'kumikoda'
__version__ = '1.0'

import sys
reload(sys)
sys.setdefaultencoding('utf8')

from modules import dbs
import pandas as pd

class Get_Data(object):
    def __init__(self):
        self.conn, self.cur = dbs.db_connect()

    def get_data(self):
        query = '''
        SELECT
        assets_user,
        IDC_type,
        room_info,
        business_name,
        count(*) AS servers_used,
        round(sum(monthbilling), 2) AS bill
        FROM
        cmdb
        WHERE
        STATUS = "使用"
        GROUP BY
        assets_user
        HAVING
        sum(monthbilling) > 0
        ORDER BY
        servers_used DESC limit 10;'''

        self.cur.execute(query)
        g_data = self.cur.fetchall()
        p_data = list(g_data)

        df = pd.DataFrame(p_data, columns=['assets_user', 'IDC_type', 'room_info', 'business_name', 'servers_used', 'bill'])
        #print df
        df.to_json(orient='index')
        #gd = pd.read_html('H:\\Python_File\\Project\\django_web\\templates\\test.html', encoding='utf-8')

        self.conn.close()
        self.cur.close()
        #print gd

if __name__ == '__main__':
    g = Get_Data()
    g.get_data()